﻿
////////////////////////////////////////////////////////////////////////////////////////////
// 文件名: ETL_SourceSystemDAL.gen.cs
//
// 模块名称: ETLSourceDAL
//
// 作者: LPC
//
// 历史记录（记录修改记录，修改文件后请添加修改记录，注明修改时间、修改人，修改内容）:
// 13-1-31    LPC    创建文件
////////////////////////////////////////////////////////////////////////////////////////////

using System;
using System.Data;
using System.Linq;
using System.Collections.Generic;
using System.Data.SqlClient;
using Common;
using HIDB;
using Model;
using System.Data.OleDb;
using System.Text;
using System.Configuration;

namespace DAL
{
    /// <summary>
    /// ETL_SourceSystem表数据访问层
    /// </summary>
    public partial class ETL_SourceSystemDAL : DB
    {
        public ETL_SourceSystemDAL(LoginInfo info)
        {
            base.loginInfo = info;
        }
        /// <summary>
        /// 根据GUID字段获取ETL_SourceSystemInfo实体对象
        /// </summary>
        /// <param name="guid">GUID</param>
        /// <returns>ETL_SourceSystemInfo实体对象</returns>
        public EtlSourceSystemInfo GetInfoById(string guid)
        {
            IList<EtlSourceSystemInfo> list = new List<EtlSourceSystemInfo>();

            //创建DbCommand对象
            HIDB.dbo cmd = base.conn();

            //添加输入输出参数
            IDataParameter[] sqlparams = { new SqlParameter("@GUID", guid) };

            //执行命令返回结果
            try
            {
                DataTable dt = cmd.StoredDataTable("Working_db.UP_ETL_SourceSystem_Select", sqlparams);
                list = CreateEtlSourceSystemInfo(dt);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_SourceSystemInfo_Insert", "UP_Sys_ETL_SourceSystemInfo_Insert", 0, 1, ex, "", "");
            }
            if (list != null && list.Count > 0)
            {
                DataTable dtc = cmd.GetDataTable(string.Format("select u.Guid+'&'+u.Username+',&'+u.EmployeeName from sysuser u inner join Working_db.SysContact c on u.Guid=c.userid where c.sysid='{0}' and c.SysType=1", guid));
                List<string> arr = new List<string>();
                foreach (DataRow dr in dtc.Rows)
                {
                    arr.Add(dr[0].ToString());
                }
                list[0].Contact = string.Join("^", arr);
                return list[0];
            }
            else
            {
                return new EtlSourceSystemInfo();
            }
        }

        /// <summary>
        /// 往ETL_SourceSystem表内添加新记录
        /// </summary>
        /// <param name="par"> </param>
        /// <returns>添加记录索引值</returns>
        public int Insert(IDataParameter[] par)
        {
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                num = int.Parse(cmd.StoredDataTable("Working_db.UP_ETL_SourceSystem_Insert", par).Rows[0][0].ToString());
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_SourceSystemInfo_Insert", "UP_Sys_ETL_SourceSystemInfo_Insert", 0, 1, ex, "", "");
            }
            return num;
        }

        /// <summary>
        /// 更新ETL_SourceSystem表记录
        /// </summary>
        public int Update(EtlSourceSystemInfo model)
        {
            int num = -1;

            //创建cmd对象
            HIDB.dbo cmd = base.conn();

            //添加输入输出参数
            SqlParameter[] sqlparams = new SqlParameter[]
            {
                new SqlParameter("@GUID", model.GUID),
                new SqlParameter("@Name", model.Name),
                new SqlParameter("@SourceSysCode", model.SourceSysCode),
                new SqlParameter("@SourceSysName", model.SourceSysName),
                new SqlParameter("@SourceType", model.SourceType),
                new SqlParameter("@CallBackName", model.CallBackName),
                new SqlParameter("@CallBackType", model.CallBackType),
                new SqlParameter("@SourceSysDescription", model.SourceSysDescription),
                new SqlParameter("@Status", model.Status),
                new SqlParameter("@UpdateUser", model.UpdateUser)
            };
            //执行命令返回结果
            try
            {
                num = int.Parse(cmd.StoredDataTable("Working_db.UP_ETL_SourceSystem_Update", sqlparams).Rows[0][0].ToString());
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_SourceSystemInfo_Insert", "UP_Sys_ETL_SourceSystemInfo_Insert", 0, 1, ex, "", "");
            }
            return num;
        }

        /// <summary>
        /// 根据GUID字段删除ETL_SourceSystemInfo表信息
        /// </summary>
        /// <param name="guid">GUID</param>
        public int Delete(IDataParameter[] par)
        {
            int num = -1;

            //创建Database对象          
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                cmd.StoredUpdate("Working_db.UP_ETL_SourceSystem_Detele", par);
                num = 1;
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_RoleInfo_Insert", "UP_Sys_RoleInfo_Insert", 0, 1, ex, "", "");
            }
            return num;
        }
        /// <summary>
        /// 添加回调配置  
        /// </summary>
        /// <param name="model">ETLCallBackConfig</param>
        public bool AddCallBack(ETLCallBackConfig model)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                SqlParameter[] par = new SqlParameter[] { 
                new SqlParameter("@GUID",model.GUID),
                new SqlParameter("@CallBackName",model.CallBackName),
                new SqlParameter("@CallBackType",model.CallBackType),
                new SqlParameter("@SysID",model.SysID),
                new SqlParameter("@SysType",model.SysType)
                };
                cmd.ExecuteSql(@"insert into [Working_db].[ETL_CallBackConfig]([GUID],[SysID],[CallBackType],[CallBackName],[SysType],[CreateTime]) 
                values(@GUID,@SysID,@CallBackType,@CallBackName,@SysType,getdate())", par);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 添加回调参数配置  
        /// </summary>
        /// <param name="model">ETLCallBackPara</param>
        public bool AddCallBackParas(ETLCallBackPara model)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                SqlParameter[] par = new SqlParameter[] { 
                new SqlParameter("@GUID",model.GUID),
                new SqlParameter("@KeyName",model.KeyName),
                new SqlParameter("@KeyValue",model.KeyValue),
                new SqlParameter("@ParentGUID",model.ParentGUID) 
                };
                cmd.ExecuteSql(@"insert into [Working_db].[ETL_CallBackParas]([GUID],[ParentGUID],[KeyName],[KeyValue],[CreateTime]) 
                values(@GUID,@ParentGUID,@KeyName,@KeyValue,getdate())", par);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 删除源系统配置信息
        /// </summary>
        /// <param name="sysID">sysID</param>
        public bool DelSysConfig(string sysID)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                SqlParameter[] para = new SqlParameter[]{
                new SqlParameter("@SysID",sysID)
                };

                cmd.ExecuteSql(@"
delete from [Working_db].[ETL_CallBackParas] where ParentGUID in (select guid from [Working_db].[ETL_CallBackConfig] where SysID=@SysID);
delete from [Working_db].[ETL_CallBackConfig] where SysID=@SysID;
delete from [Working_db].[SysContact] where SysID=@SysID", para);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据实体获取ETL_SourceSystemInfo实体List对象
        /// </summary>
        /// <param name="par"> </param>
        /// <returns>ETL_SourceSystemInfo实体对象</returns>
        public IList<EtlSourceSystemInfo> GetList(IDataParameter[] par)
        {
            IList<EtlSourceSystemInfo> list = new List<EtlSourceSystemInfo>();

            //创建DbCommand对象
            HIDB.dbo cmd = base.conn();
            try
            {
                DataTable dt = cmd.StoredDataTable("Working_db.UP_ETL_SourceSystem_GetList", par);
                //list = CreateEtlSourceSystemInfo(dt);
                list = dt.MapToList<EtlSourceSystemInfo>();
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_SourceSystemInfo_Select", "UP_Sys_ETL_SourceSystemInfo_Select", 0, 1, ex, "",
                                "");
            }
            if (list != null)
            {
                return list;
            }
            else
            {
                return null;
            }
        }
        public int GetETLStatusBySysID(string sysID)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                SqlParameter[] para = new SqlParameter[] { 
                new SqlParameter("",sysID)
                };
                DataTable dt = cmd.GetDataTable("");
                if (dt != null && dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0][0]);
                }
                else
                    return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据实体获取ETL_SourceSystemInfo实体List对象
        /// </summary>
        /// <param name="par"> </param>
        /// <returns>ETL_SourceSystemInfo实体对象</returns>
        public int GetListCount(IDataParameter[] par)
        {
            int rowCount = 0;
            var list = new List<EtlSourceSystemInfo>();

            //创建DbCommand对象
            HIDB.dbo cmd = base.conn();

            //执行命令返回结果
            try
            {
                DataTable dt = cmd.StoredDataTable("Working_db.UP_ETL_SourceSystem_GetCount", par);
                if (dt != null && dt.Rows.Count > 0)
                    rowCount = Convert.ToInt32(dt.Rows[0][0]);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_SourceSystemInfo_Select", "UP_Sys_ETL_SourceSystemInfo_Select", 0, 1, ex, "",
                                "");
            }
            return rowCount;
        }

        /// <summary>
        /// 生成ETL_SourceSystemInfo实体对象
        /// </summary>
        private IList<EtlSourceSystemInfo> CreateEtlSourceSystemInfo(DataTable dt)
        {
            return Common.DBConvert.DataTableToList<EtlSourceSystemInfo>(dt);
        }
        public DataTable GetPathList(string sql, SqlParameter[] para)
        {
            HIDB.dbo cmd = base.conn();

            try
            {
                return cmd.GetDataTable(sql, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int UpDBStatus(string id, int status)
        {
            HIDB.dbo cmd = base.conn();

            try
            {
                SqlParameter[] para = new SqlParameter[]{
                new SqlParameter("@GUID",id),
                new SqlParameter("@Status",status)
                };
                return cmd.ExecuteSql("update [Working_db].ETL_DbConfig set [Status]=@Status where GUID=@GUID", para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int InsertJobConfigStep(IDataParameter[] parasJobConfig)
        {
            HIDB.dbo cmd = base.conn();
            var i = 0;
            try
            {
                cmd.StoredUpdate("working_db.[UP_ETL_SYS_JobConfig_Step_Insert_Staging_db]", parasJobConfig);
                i = 1;
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_ETL_SYS_JobConfig_Step_Insert", "UP_ETL_SYS_JobConfig_Step_Insert", 0, 1, ex, "", "");
            }
            return i;
        }

        public int InsertCallBack(IDataParameter[] callBackParas)
        {
            HIDB.dbo cmd = base.conn();
            var i = 0;
            try
            {
                cmd.StoredUpdate("working_db.[UP_ETL_CallBackConfig_Insert]", callBackParas);
                i = 1;
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_ETL_CallBackConfig_Insert", "UP_ETL_CallBackConfig_Insert", 0, 1, ex, "", "");
            }
            return i;
        }

        public int delCBParas(SqlParameter[] delCbParas)
        {
            HIDB.dbo cmd = base.conn();
            var i = 0;
            try
            {
                cmd.StoredUpdate("working_db.[UP_ETL_CallBackConfig_Delete]", delCbParas);
                i = 1;
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_ETL_CallBackConfig_Delete", "UP_ETL_CallBackConfig_Delete", 0, 1, ex, "", "");
            }
            return i;
        }

        public List<ETL_Source_View_ColumnConfigInfo> GetTableInfoList(string tbName, string connstr, string connType, string tbOwer)
        {
            //创建数据库对象
            HIDB.dbo con;
            DataTable dt = new DataTable();
            var paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@TableName", tbName);
            string sql = string.Format("select * from {0} where 1<>1", tbName);//owner.tablename

            switch (connType)
            {
                case "1":
                    con = HIDB.common.make(HIDB.DBType.SQLServer, connstr);
                    dt = con.GetDataTable(sql);
                    break;
                case "2":
                    dt = DB.DBOracleConn(connstr, sql.Replace("[", "\"").Replace("]", "\""));//HIDB.common.make(HIDB.DBType.Oracle, connstr);
                    break;
                case "3":
                    string accConnstr = string.Format("Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Persist Security Info=False;", connstr);
                    con = HIDB.common.make(HIDB.DBType.OleDB, accConnstr);
                    dt = con.GetDataTable(sql);
                    break;
                case "4":
                    // sql = string.Format("select column_name from information_schema.columns a where a.table_schema='{0}' and a.table_name='{1}'", tbOwer, targetID.Replace(tbOwer+".",""));
                    dt = DB.DBMySqlConn(connstr, sql.Replace("[", "`").Replace("]", "`"));
                    break;
                case "8":
                    dt = DB.DBNpgsqlConn(connstr, sql.Replace("[", "\"").Replace("]", "\""));
                    break;
                case "5":
                    dt = DB.DBExcelConn(connstr, sql);
                    break;
            }
            var list = new List<ETL_Source_View_ColumnConfigInfo>();

            int ciso = 0;
            foreach (DataColumn dataParameter in dt.Columns)
            {
                var sourceViewColumn = new ETL_Source_View_ColumnConfigInfo();
                sourceViewColumn.ColumnName = dataParameter.ColumnName;
                sourceViewColumn.ColumnIsExtract = 1;
                if (connType == "3" || connType == "5")
                    sourceViewColumn.ColumnType = "nvarchar(max)";
                sourceViewColumn.ColumnIsPK = 0;
                sourceViewColumn.ColumnIsOrder = 0;
                sourceViewColumn.ColumnOrderID = ciso;
                list.Add(sourceViewColumn);
                ciso++;
            }

            return list;
        }

        public DataTable GetSourceViewTableBySql(string connstr, string connTypeStr)
        {
            DataTable dt = new DataTable();
            switch (connTypeStr)
            {
                case "1":
                    HIDB.dbo con = HIDB.common.make(HIDB.DBType.SQLServer, connstr);
                    dt = con.GetDataTable("select a.name as table_name,SCHEMA_NAME(a.schema_id) as owner from sys.objects a where a.type in ('v','u') order by owner,table_name");
                    break;
                case "2":
                    dt = DB.DBOracleConn(connstr, @"select distinct * from (
select b.table_name as table_name,b.owner from user_tab_privs b where b.privilege='SELECT' 
union all select b.view_name,b.owner from user_views a inner join all_views b on a.view_name=b.view_name 
union all select b.table_name,b.owner from user_tables a inner join all_tables b on a.table_name=b.table_name) a order by a.table_name");
                    break;
                case "3":
                    string accConnstr = string.Format("Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Persist Security Info=False;", connstr);
                    HIDB.dbo acc = HIDB.common.make(HIDB.DBType.OleDB, accConnstr);
                    dt = acc.GetSchema("tables");
                    break;
                case "4":
                    dt = DB.DBMySqlConn(connstr, "select a.table_name,a.table_schema as owner from information_schema.tables a order by a.table_name");
                    break;
                case "8":
                    dt = DB.DBNpgsqlConn(connstr, "select a.table_name,a.table_schema as owner from information_schema.tables a where a.table_schema='public' order by a.table_name");
                    break;
                case "5":
                    string exlConnstr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES\";", connstr);
                    OleDbConnection cn = new OleDbConnection(exlConnstr);
                    cn.Open();
                    dt = cn.GetSchema("tables");
                    if (dt.Rows.Count > 0)
                    {
                        int count = dt.Rows.Count;
                        for (int i = 0; i < count; i++)
                        {
                            string name = dt.Rows[i]["Table_Name"].ToString();
                            if (connstr.LastIndexOf(".xlsx") == connstr.Length - 5)
                            {

                            }
                            else
                            {
                                if (name.Length - name.LastIndexOf("$") == 1)
                                {
                                    dt.Rows.Remove(dt.Rows[i]);
                                    count--;
                                    i--;
                                }
                            }
                        }
                    }
                    cn.Close();
                    cn.Dispose();
                    break;
            }
            return dt;
        }

        public DataTable GetALLColumnType()
        {
            HIDB.dbo con = base.conn();
            var dt = new DataTable();
            try
            {
                dt = con.StoredDataTable("dbo.UP_GetColumnType ");

            }
            catch (Exception ex)
            {
                Logger.LogError("UP_ETL_CallBackConfig_Delete", "UP_ETL_CallBackConfig_Delete", 0, 1, ex, "", "");
            }
            return dt;

        }

        public int SourceDelete(SqlParameter[] par)
        {
            HIDB.dbo cmd = base.conn();
            var i = 0;
            try
            {
                cmd.StoredUpdate("working_db.[UP_ETL_SourceSystem_Delete]", par);
                i = 1;
            }
            catch (Exception ex)
            {
                Logger.LogError("[UP_ETL_SourceSystem_Delete]", "[UP_ETL_SourceSystem_Delete]", 0, 1, ex, "", "");
            }
            return i;
        }
        public bool SourceDeleteCheck(string sourID)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                IDataParameter[] queryParas = new SqlParameter[1];
                queryParas[0] = new SqlParameter("@SourceSystemGUID", sourID);

                DataTable dt = cmd.StoredDataTable("working_db.[UP_ETL_SourceDeleteCheck]", queryParas);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return Convert.ToBoolean(Convert.ToInt32(dt.Rows[0][0].ToString()));
                }
                else
                    return false;
            }
            catch (Exception ex)
            {
                Logger.LogError("[UP_ETL_SourceSystem_Delete]", "[UP_ETL_SourceSystem_Delete]", 0, 1, ex, "", "");
                return false;
            }
        }

        public DataTable UpateTask(SqlParameter[] par)
        {
            HIDB.dbo con = base.conn();
            var dt = new DataTable();
            try
            {
                dt = con.StoredDataTable("[Working_db].[UP_ETL_Task_Update] ", par);

            }
            catch (Exception ex)
            {
                Logger.LogError("UP_ETL_CallBackConfig_Delete", "UP_ETL_CallBackConfig_Delete", 0, 1, ex, "", "");
            }
            return dt;
        }

        public DataTable GetSourceViewBySystemID(SqlParameter[] paras1)
        {
            DataTable dt = new DataTable();
            HIDB.dbo cmd = base.conn();
            try
            {
                dt = cmd.StoredDataTable("Working_db.GetSourceViewBySystemID", paras1);

            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_SourceSystemInfo_Select", "UP_Sys_ETL_SourceSystemInfo_Select", 0, 1, ex, "",
                                "");
            }

            return dt;
        }

        public int SourceUpdateStatus(SqlParameter[] par)
        {
            HIDB.dbo cmd = base.conn();
            var i = 0;
            try
            {
                cmd.StoredUpdate("working_db.[UP_ETL_SourceSystem_UpdateStatus]", par);
                i = 1;
            }
            catch (Exception ex)
            {
                Logger.LogError("[UP_ETL_SourceSystem_Delete]", "[UP_ETL_SourceSystem_Delete]", 0, 1, ex, "", "");
            }
            return i;
        }

        public bool GetSourceSystemByName(SqlParameter[] par)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                DataTable dt = new DataTable();
                dt = cmd.GetDataTable("select GUID from  [Working_db].ETL_SourceSystem where SourceSysName=@SourceSysName", par);
                if (dt.Rows.Count > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetTableList(IDataParameter[] dataparams)
        {
            DataTable dt = new DataTable();
            try
            {
                HIDB.dbo cmd = base.conn();
                dt = cmd.StoredDataTable("[Working_db].UP_ETL_DbConfig_GetInfo", dataparams);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_SourceSystemInfo_Select", "UP_Sys_ETL_SourceSystemInfo_Select", 0, 1, ex, "", "");
            }
            return dt;
        }

        public IList<ETLDbConfig> GetETLDbConfigList(IDataParameter[] dataparams)
        {
            IList<ETLDbConfig> result = new List<ETLDbConfig>();
            try
            {
                HIDB.dbo cmd = base.conn();
                DataTable dt = cmd.GetDataTable(@"SELECT * FROM [Working_db].[ETL_DbConfig] where [SysID]=@SysID order by DbSortID", dataparams);
                result = Common.DBConvert.DataTableToList<ETLDbConfig>(dt);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_SourceSystemInfo_Select", "UP_Sys_ETL_SourceSystemInfo_Select", 0, 1, ex, "", "");
            }
            return result;
        }
        public IList<ETLDbConfig> GetETLDbConfigList(SqlParameter[] dataparams)
        {
            IList<ETLDbConfig> result = new List<ETLDbConfig>();
            try
            {
                HIDB.dbo cmd = base.conn();
                DataTable dt = cmd.GetDataTable(@"SELECT * FROM [Working_db].[ETL_DbConfig] where [SysID]=@SysID 
and DbDataSource like case when(len(@Host)=0) then DbDataSource else '%'+@Host+'%' end
and DbDatabase like case when(len(@Server)=0) then DbDatabase else '%'+@Server+'%' end
and Description like case when(len(@Description)=0) then [Description] else '%'+@Description+'%' end
order by DbSortID", dataparams);
                result = Common.DBConvert.DataTableToList<ETLDbConfig>(dt);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_SourceSystemInfo_Select", "UP_Sys_ETL_SourceSystemInfo_Select", 0, 1, ex, "", "");
            }
            return result;
        }
        public IList<ETLCallBackConfig> GetCallBackConfigList(IDataParameter[] dataparams)
        {
            IList<ETLCallBackConfig> result = new List<ETLCallBackConfig>();
            try
            {
                HIDB.dbo cmd = base.conn();
                DataTable dt = cmd.GetDataTable(@"SELECT GUID,CallBackName,CallBackType,SysType FROM [Working_db].[ETL_CallBackConfig] where [SysID]=@SysID", dataparams);
                result = Common.DBConvert.DataTableToList<ETLCallBackConfig>(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        public IList<ETLCallBackPara> GetCallBackParasList(IDataParameter[] dataparams)
        {
            IList<ETLCallBackPara> result = new List<ETLCallBackPara>();
            try
            {
                HIDB.dbo cmd = base.conn();
                DataTable dt = cmd.GetDataTable(@"select KeyName,KeyValue from [Working_db].ETL_CallBackParas where ParentGUID=@ParentGUID", dataparams);
                result = Common.DBConvert.DataTableToList<ETLCallBackPara>(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        public IList<ETLWDCConfig> GetWDCConfigList(IDataParameter[] dataparams)
        {
            IList<ETLWDCConfig> result = new List<ETLWDCConfig>();
            try
            {
                HIDB.dbo cmd = base.conn();
                DataTable dt = cmd.GetDataTable(@"SELECT [GUID],[SysID],[WildCard],[FileExt],[ExtractOrder] FROM [Working_db].[Source_DbWildCard] where [SysID]=@SysID", dataparams);
                return Common.DBConvert.DataTableToList<ETLWDCConfig>(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int InsertDbConfig(IDataParameter[] sqlparams)
        {
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                cmd.StoredUpdate("working_db.[UP_ETL_DbConfig_Insert]", sqlparams);
                num = 1;
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_ETL_DbConfig_Insert", "UP_ETL_DbConfig_Insert", 0, 1, ex, "", "");
            }
            return num;
        }

        public IList<ETLDbConfig> LoadListBySysID(string sysID)
        {
            IList<ETLDbConfig> result = new List<ETLDbConfig>();
            try
            {
                HIDB.dbo cmd = base.conn();
                string sql = "SELECT * FROM [Working_db].[ETL_DbConfig] WHERE [SysID]='" + sysID + "'";
                DataTable dt = cmd.GetDataTable(sql);
                result = DBConvert.DataTableToList<ETLDbConfig>(dt);
            }
            catch (Exception)
            {

                throw;
            }
            return result;
        }

        #region 新的函数

        #region 源系统管理 EtlSourceSystemInfo

        /// <summary>
        /// 根据GUID字段获取ETL_SourceSystemInfo实体对象
        /// </summary>
        /// <param name="guid">GUID</param>
        /// <returns>ETL_SourceSystemInfo实体对象</returns>
        public EtlSourceSystemInfo GetEtlSourceSystemInfoById(string guid)
        {
            IList<EtlSourceSystemInfo> list = new List<EtlSourceSystemInfo>();

            //创建DbCommand对象
            HIDB.dbo cmd = base.conn();

            //添加输入输出参数
            IDataParameter[] cmdParms = { new SqlParameter("@GUID", guid) };

            //执行命令返回结果
            try
            {
                string strSql = @"SELECT [GUID]
      ,[Name],[SourceSysCode],[SourceSysName],[SourceType],[CallBackName]
      ,[CallBackType],[SourceSysDescription],[Status],[IsDelete],[CreateUser]
      ,CONVERT(varchar(100), CreateTime, 23) as CreateTime,[UpdateUser],[UpdateTime]
      ,[DeleteUser],[DeleteTime] 
FROM [Working_db].ETL_SourceSystem WHERE GUID=@GUID";
                DataTable dts = cmd.GetDataTable(strSql, cmdParms);
                list = dts.MapToList<EtlSourceSystemInfo>();
            }
            catch (Exception ex)
            {
                Logger.LogError("GetEtlSourceSystemInfoById", "GetEtlSourceSystemInfoById", 0, 1, ex, "", "");
            }
            if (list != null && list.Count > 0)
            {
                var sourceInfo = list[0];
                //sourceInfo.ETLContacts = this.GetContactById(guid);
                //sourceInfo.ETLDbConfigs = this.GetDbConfigBySysID(guid);
                return sourceInfo;
            }
            else
            {
                return new EtlSourceSystemInfo();
            }
        }

        public List<EtlSourceSystemInfo> GetByNameAndCode(string sourceSysName, string sourceSysCode)
        {
            List<EtlSourceSystemInfo> result = new List<EtlSourceSystemInfo>();
            try
            {
                string strSql = "SELECT FROM [Working_db].[ETL_SourceSystem] WHERE 1=1";
                List<IDataParameter> cmdParms = new List<IDataParameter>();
                if (!string.IsNullOrEmpty(sourceSysName))
                {
                    strSql += " AND SourceSysName =@SourceSysName";
                    cmdParms.Add(new SqlParameter("@SourceSysName", sourceSysName));
                }
                if (!string.IsNullOrEmpty(sourceSysCode))
                {
                    strSql += " AND SourceSysCode =@SourceSysCode";
                    cmdParms.Add(new SqlParameter("@SourceSysCode", sourceSysCode));
                }
                HIDB.dbo cmd = base.conn();
                DataTable dt = cmd.GetDataTable(strSql, cmdParms.ToArray());
                result = dt.MapToList<EtlSourceSystemInfo>();
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_SourceSystemInfo_Select", "UP_Sys_ETL_SourceSystemInfo_Select", 0, 1, ex, "", "");
            }
            return result;

        }

        public List<EtlSourceSystemInfo> GetByNameWithOutGUID(string guid, string sourceSysName)
        {
            List<EtlSourceSystemInfo> result = new List<EtlSourceSystemInfo>();
            try
            {
                string strSql = "SELECT * FROM [Working_db].[ETL_SourceSystem] WHERE 1=1";
                List<IDataParameter> cmdParms = new List<IDataParameter>();

                strSql += " AND SourceSysName =@SourceSysName";
                cmdParms.Add(new SqlParameter("@SourceSysName", sourceSysName));

                strSql += " AND GUID!=@GUID";
                cmdParms.Add(new SqlParameter("@GUID", guid));

                HIDB.dbo cmd = base.conn();
                DataTable dt = cmd.GetDataTable(strSql, cmdParms.ToArray());
                result = dt.MapToList<EtlSourceSystemInfo>();
            }
            catch (Exception ex)
            {
                Logger.LogError("GetByNameWithOutGUID", "GetByNameWithOutGUID", 0, 1, ex, "", "");
            }
            return result;

        }


        public int Insert(EtlSourceSystemInfo sourceInfo)
        {
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                string strSql = @"INSERT INTO [Working_db].[ETL_SourceSystem]
		            ([GUID],Name,SourceSysCode,SourceSysName,SourceType,SourceSysDescription,CallBackName
		            ,CallBackType,[Status],CreateUser,CreateTime,IsDelete) 
		            VALUES(@GUID,@Name,@SourceSysCode,@SourceSysName,@SourceType,@SourceSysDescription,@CallBackName
		            ,@CallBackType,@Status,@CreateUser,GETDATE(),0)";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",sourceInfo.GUID)
                     ,new SqlParameter("@Name",sourceInfo.Name??string.Empty)
                     ,new SqlParameter("@SourceSysCode",sourceInfo.SourceSysCode??string.Empty)
                     ,new SqlParameter("@SourceSysName",sourceInfo.SourceSysName??string.Empty)
                     ,new SqlParameter("@SourceType",sourceInfo.SourceType)
                     ,new SqlParameter("@CallBackName",sourceInfo.CallBackName??string.Empty)
                     ,new SqlParameter("@CallBackType",sourceInfo.CallBackType)
                     ,new SqlParameter("@SourceSysDescription",sourceInfo.SourceSysDescription??string.Empty)
                     ,new SqlParameter("@Status",sourceInfo.Status)
                     ,new SqlParameter("@CreateUser",sourceInfo.CreateUser??string.Empty)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_SourceSystemInfo_Insert", "UP_Sys_ETL_SourceSystemInfo_Insert", 0, 1, ex, "", "");
            }
            return num;
        }

        /// <summary>
        /// 更新ETL_SourceSystem表记录
        /// </summary>
        public int Update2(EtlSourceSystemInfo sourceInfo)
        {
            int num = -1;

            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            string strSql = @"
        UPDATE [Working_db].[ETL_SourceSystem] 
		SET 
		 Name=@Name
		,SourceSysCode=@SourceSysCode
		,SourceSysName=@SourceSysName
		,SourceType=@SourceType
		,SourceSysDescription=@SourceSysDescription
		,CallBackName=@CallBackName
		,CallBackType= @CallBackType
		,UpdateUser=@UpdateUser
		,UpdateTime=GETDATE()
		,[Status]=@Status 
		WHERE [GUID]=@GUID;
if(@DBType='2')
begin
if exists (select 1 from
[Working_db].[ETL_Task_Details]
where JobStepID in (SELECT  groupID
  FROM [Working_db].[ETL_Target_ViewConfig]
  where groupID is not null and guid in ( 
  select TargetViewID from [Working_db].[ViewMapping] where SourceViewID in (
  select GUID from  [Working_db].[ETL_Source_ViewConfig] where [SourceSystemID]=@GUID
  ))))
  begin
  exec working_db.CreateSysJob
  end
end
";
            // ;DELETE FROM [Working_db].[ETL_DbConfig] WHERE SysID=@GUID
            //添加输入输出参数
            SqlParameter[] cmdParms = new SqlParameter[]
            {
                new SqlParameter("@GUID", sourceInfo.GUID)
                 ,new SqlParameter("@Name",sourceInfo.Name??string.Empty)
                     ,new SqlParameter("@SourceSysCode",sourceInfo.SourceSysCode??string.Empty)
                     ,new SqlParameter("@SourceSysName",sourceInfo.SourceSysName??string.Empty)
                     ,new SqlParameter("@SourceType",sourceInfo.SourceType)
                     ,new SqlParameter("@CallBackName",sourceInfo.CallBackName??string.Empty)
                     ,new SqlParameter("@CallBackType",sourceInfo.CallBackType)
                     ,new SqlParameter("@SourceSysDescription",sourceInfo.SourceSysDescription??string.Empty)
                     ,new SqlParameter("@Status",sourceInfo.Status)
                     ,new SqlParameter("@CreateUser",sourceInfo.CreateUser??string.Empty)
                     ,new SqlParameter("@UpdateUser",sourceInfo.UpdateUser??string.Empty)
                     ,new SqlParameter("@DBType",sourceInfo.DBType??"1")
            };
            //执行命令返回结果
            try
            {
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_SourceSystemInfo_Insert", "UP_Sys_ETL_SourceSystemInfo_Insert", 0, 1, ex, "", "");
            }
            return num;
        }
        #endregion

        #region ETLDbConfig

        public List<ETLDbConfig> GetDbConfigBySysID(string sysID)
        {
            List<ETLDbConfig> result = new List<ETLDbConfig>();
            try
            {
                HIDB.dbo cmd = base.conn();
                string strSql = "SELECT * FROM [Working_db].[ETL_DbConfig] WHERE [SysID]=@SysID AND (IsDelete=0 OR IsDelete IS NULL) order by DbSortID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@SysID",sysID)
                };
                DataTable dt = cmd.GetDataTable(strSql, cmdParms);
                result = dt.MapToList<ETLDbConfig>();
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }

        public ETLDbConfig GetDbConfigByID(string ID)
        {
            List<ETLDbConfig> result = new List<ETLDbConfig>();
            try
            {
                HIDB.dbo cmd = base.conn();
                string strSql = "SELECT * FROM [Working_db].[ETL_DbConfig] WHERE [GUID]=@GUID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",ID)
                };
                DataTable dt = cmd.GetDataTable(strSql, cmdParms);
                result = dt.MapToList<ETLDbConfig>();
                if (result.Count > 0)
                    return result[0];
                else
                    return new ETLDbConfig();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int AddDbConfig(ETLDbConfig dbConfigInfo)
        {
            int num = -1;
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"INSERT INTO [Working_db].[ETL_DbConfig]  ([GUID],SysID,DbSortID,DbType,DbConnectionStr,DbDataSource,DbDatabase,DbUserID,DbPassword,DbPort,DbSID,CreateUser,Description,Status,CreateTime) 
VALUES(@GUID,@SysID,@DbSortID,@DbType,@DbConnectionStr,@DbDataSource,@DbDatabase,@DbUserID,@DbPassword,@DbPort,@DbSID,@CreateUser,@Description,1,GETDATE())";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",dbConfigInfo.GUID)
                     ,new SqlParameter("@SysID",dbConfigInfo.SysID)
                     ,new SqlParameter("@DbSortID",dbConfigInfo.DbSortID)
                     ,new SqlParameter("@DbType",dbConfigInfo.DbType)
                     ,new SqlParameter("@DbConnectionStr",dbConfigInfo.DbConnectionStr)
                     ,new SqlParameter("@DbDataSource",dbConfigInfo.DbDataSource)
                     ,new SqlParameter("@DbDatabase",dbConfigInfo.DbDatabase)
                     ,new SqlParameter("@DbUserID",dbConfigInfo.DbUserID)
                     ,new SqlParameter("@DbPassword",dbConfigInfo.DbPassword)
                     ,new SqlParameter("@DbPort",dbConfigInfo.DbPort)
                     ,new SqlParameter("@DbSID",dbConfigInfo.DbSID) 
                     ,new SqlParameter("@Description",dbConfigInfo.Description)   
                     ,new SqlParameter("@CreateUser",dbConfigInfo.CreateUser??string.Empty)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return num;
        }
        public int UpdateBigDateTagConfig(string sysID, string DBID)
        {
            int num = -1;
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"insert into [Working_db].[BigDateTagConfig]
select newid(),v.guid,@DBID,case when(c.ColumnIsBigDataTag=1) then @DBID else '' end,getdate(),'','' from Working_db.ETL_Source_ViewConfig v left join Working_db.ETL_Source_View_ColumnConfig c on
v.GUID=c.viewID and c.ColumnIsBigDataTag=1 and c.ColumnName='MDP_SourceType' where v.SourceSystemID=@sysID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@sysID",sysID)
                   ,new SqlParameter("@DBID",DBID)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return num;
        }
        public int UpdateDbConfig(ETLDbConfig dbConfigInfo)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"update [Working_db].[ETL_DbConfig] set DbConnectionStr=@DbConnectionStr,DbDataSource=@DbDataSource,
DbUserID=@DbUserID,DbPassword=@DbPassword,Description=@Description where GUID=@GUID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",dbConfigInfo.GUID) 
                     ,new SqlParameter("@DbConnectionStr",dbConfigInfo.DbConnectionStr)
                     ,new SqlParameter("@DbDataSource",dbConfigInfo.DbDataSource) 
                     ,new SqlParameter("@DbUserID",dbConfigInfo.DbUserID)
                     ,new SqlParameter("@DbPassword",dbConfigInfo.DbPassword) 
                     ,new SqlParameter("@Description",dbConfigInfo.Description) 
                };
                cmd.ExecuteSql(strSql, cmdParms);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int AddWDCConfig(ETLWDCConfig model)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"INSERT INTO [Working_db].[Source_DbWildCard] ([GUID],SysID,WildCard,FileExt,ExtractOrder,CreateTime) 
VALUES(@GUID,@SysID,@WildCard,@FileExt,@ExtractOrder,GETDATE())";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",model.GUID)
                     ,new SqlParameter("@SysID",model.SysID)
                     ,new SqlParameter("@WildCard",model.WildCard)
                     ,new SqlParameter("@FileExt",model.FileExt)
                     ,new SqlParameter("@ExtractOrder",model.ExtractOrder) 
                };
                return cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int DeleteWDCConfig(string guid)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"delete from [Working_db].[Source_DbWildCard] where GUID=@GUID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",guid) 
                };
                return cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int UpdateWDCConfig(ETLWDCConfig model)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"Update [Working_db].[Source_DbWildCard]
set WildCard=@WildCard,FileExt=@FileExt,ExtractOrder=@ExtractOrder
where [GUID]=@GUID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",model.GUID) 
                     ,new SqlParameter("@WildCard",model.WildCard)
                     ,new SqlParameter("@FileExt",model.FileExt)
                     ,new SqlParameter("@ExtractOrder",model.ExtractOrder) 
                };
                return cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int UpDateDbConfig(ETLDbConfig dbConfigInfo)
        {
            //"working_db.[UP_ETL_DbConfig_Insert]", sqlparams);
            //创建cmd对象
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                string strSql = @"UPDATE [Working_db].[ETL_DbConfig] 
SET SysID=@SysID,DbSortID=@DbSortID,DbType=@DbType,DbConnectionStr=@DbConnectionStr,
DbDataSource=@DbDataSource,DbDatabase=@DbDatabase,DbUserID=@DbUserID,DbPassword=@DbPassword,DbPort=@DbPort,DbSID=@DbSID,
UpdateUser=@UpdateUser,UpdateTime=GETDATE()
WHERE GUID=@GUID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",dbConfigInfo.GUID)
                     ,new SqlParameter("@SysID",dbConfigInfo.SysID)
                     ,new SqlParameter("@DbSortID",dbConfigInfo.DbSortID)
                     ,new SqlParameter("@DbType",dbConfigInfo.DbType)
                     ,new SqlParameter("@DbConnectionStr",dbConfigInfo.DbConnectionStr)
                     ,new SqlParameter("@DbDataSource",dbConfigInfo.DbDataSource)
                     ,new SqlParameter("@DbDatabase",dbConfigInfo.DbDatabase)
                     ,new SqlParameter("@DbUserID",dbConfigInfo.DbUserID)
                     ,new SqlParameter("@DbPassword",dbConfigInfo.DbPassword)
                     ,new SqlParameter("@DbPort",dbConfigInfo.DbPort)
                     ,new SqlParameter("@DbSID",dbConfigInfo.DbSID)
                     ,new SqlParameter("@UpdateUser",dbConfigInfo.UpdateUser??string.Empty)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_ETL_UpDateDbConfig", "UP_ETL_UpDateDbConfig", 0, 1, ex, "", "");
            }
            return num;
        }

        public int DeleteDbConfig(ETLDbConfig dbConfigInfo)
        {
            int num = -1;
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"delete from [Working_db].[ETL_DbConfig] WHERE GUID=@GUID;
delete from [Working_db].[BigDateTagConfig] where DBID=@GUID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",dbConfigInfo.GUID)  
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_ETL_UpDateDbConfig", "UP_ETL_UpDateDbConfig", 0, 1, ex, "", "");
            }
            return num;
        }

        public int DeleteDbConfigBySysID(string sysID)
        {
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"DELETE FROM [Working_db].[ETL_DbConfig] where SysID=@SysID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@SysID",sysID)
                    // ,new SqlParameter("@SysType",1)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return num;
        }

        #endregion

        #region 联系人信息
        /// <summary>
        /// 获取源系统的联系人信息
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        public IList<SysUserInfo> GetContactById(IDataParameter[] dataparams)
        {
            IList<SysUserInfo> list = new List<SysUserInfo>();

            HIDB.dbo cmd = base.conn();
            try
            {
                string dataBase = ConfigurationManager.AppSettings["DataBase"].ToString();
                string strSql = string.Format("select u.[Guid],[EmployeeCode],[EmployeeName],[OfficeTel],[HomeTel],[Mobile],[Gender],[OfficeEmail],[Username],[CompanyName],[Department],[Post],[Pwd],[UserType],[Status] from {0}.dbo.SysUser u inner join Working_db.SysContact c on u.Guid=c.UserID where c.SysID=@SysID", dataBase);
                DataTable dtc = cmd.GetDataTable(strSql, dataparams);
                list = dtc.MapToList<Model.SysUserInfo>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        public int DeleteContact(string userID, string sysID)
        {
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"DELETE FROM [Working_db].SysContact where UserID=@userID and SysID=@sysID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@userID",userID),
                    new SqlParameter("@sysID",sysID)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return num;
        }

        /// <summary>
        /// 删除源系统的联系人信息
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        public int DeleteContactBySysID(string sysID)
        {
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"DELETE FROM [Working_db].SysContact where SysID=@SysID and SysType=@SysType";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@SysID",sysID)
                     ,new SqlParameter("@SysType",1)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return num;
        }

        public int DeleteContactBySysID(string sysID, int sysType)
        {
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"DELETE FROM [Working_db].SysContact where SysID=@SysID and SysType=@SysType";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@SysID",sysID)
                     ,new SqlParameter("@SysType",sysType)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return num;
        }


        public int AddContact(EtlContactInfo contactInfo)
        {
            //创建cmd对象
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                string strSql = @"INSERT INTO [Working_db].SysContact([GUID],UserID,SysID,SysType,CreateTime) 
	VALUES(NEWID(),@UserID,@SysID,@SysType ,GETDATE())";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",contactInfo.GUID)
                     ,new SqlParameter("@UserID",contactInfo.UserID)
                     ,new SqlParameter("@SysID",contactInfo.SysID)
                     ,new SqlParameter("@SysType",contactInfo.SysType)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_SourceSystemInfo_AddContact", "UP_Sys_ETL_SourceSystemInfo_AddContact", 0, 1, ex, "", "");
            }
            return num;
        }

        #endregion

        #region ETL_Source_ViewConfig

        public List<ETL_Source_ViewConfigInfo> GetSourceViewConfigBySystemID(string sysID)
        {
            List<ETL_Source_ViewConfigInfo> result = new List<ETL_Source_ViewConfigInfo>();
            try
            {
                HIDB.dbo cmd = base.conn();
                string strSql = "SELECT * FROM Working_db.ETL_Source_ViewConfig WHERE SourceSystemID=@SysID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@SysID",sysID)
                };
                DataTable dt = cmd.GetDataTable(strSql, cmdParms);
                result = dt.MapToList<ETL_Source_ViewConfigInfo>();
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_SourceSystemInfo_Select", "UP_Sys_ETL_SourceSystemInfo_Select", 0, 1, ex, "", "");
            }
            return result;
        }
        #endregion

        #region ETL_Source_View_ColumnConfig



        #endregion

        #region VW_ColumnTypeList
        public List<VW_ColumnTypeInfo> GetALLColumnTypeList()
        {
            List<VW_ColumnTypeInfo> result = new List<VW_ColumnTypeInfo>();
            try
            {
                HIDB.dbo cmd = base.conn();
                string strSql = @"SELECT [ColumnType],[ColumnTypeName] FROM [Working_db].[VW_ColumnTypeList]";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                };
                DataTable dt = cmd.GetDataTable(strSql, cmdParms);
                result = dt.MapToList<VW_ColumnTypeInfo>();
            }
            catch (Exception ex)
            {
                Logger.LogError("GetALLColumnTypeList", "GetALLColumnTypeList", 0, 1, ex, "", "");
            }
            return result;

        }
        #endregion

        #region 公共存储过程单元

        public int InsertJobConfigStep(string viewID, string createUser)
        {
            HIDB.dbo cmd = base.conn();
            var num = -1;
            try
            {
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@JobID", "E9EDEA98-5FF5-4ADB-8801-2989E7543137"),
                    new SqlParameter("@StepID", viewID),
                    new SqlParameter("@StepOrderID", 1),
                    new SqlParameter("@StepName", "执行抽取动作"),
                    new SqlParameter("@StepDescription", "执行抽取动作"),
                    new SqlParameter("@StepStatus", 1),
                    new SqlParameter("@StepType","E"), 
                    new SqlParameter("@CreateUser",createUser??string.Empty),
                    new SqlParameter("@CreateTime", DateTime.Now)
                };

                cmd.StoredUpdate("working_db.[UP_ETL_SYS_JobConfig_Step_Insert_Staging_db]", cmdParms);
                num = 1;
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_ETL_SYS_JobConfig_Step_Insert", "UP_ETL_SYS_JobConfig_Step_Insert", 0, 1, ex, "", "");
            }
            return num;
        }

        #endregion

        /// <summary>
        ///  取源系统的表数据
        /// </summary> 
        /// <returns></returns>
        public DataTable GetSourceTableData(string viewName, string connstr, string connType, string sort, string asc, string[] sqlExt, IDataParameter[] para)
        {
            //创建数据库对象
            HIDB.dbo con = HIDB.common.make();
            DataTable dtResult = new DataTable();

            string orderBy = string.Empty;
            if (!string.IsNullOrEmpty(sort))
            {
                orderBy = string.Format(" ORDER BY {0} {1}", sort, asc == "1" ? "asc" : "desc");
            }
            string sqlWhere = string.Empty;
            if (sqlExt.Length > 0)
            {
                sqlWhere = string.Format(" and {0}", string.Join(" and ", sqlExt));
            }
            string sql = string.Format("SELECT top 13 * FROM {0} where 1=1 {1} {2}", viewName, sqlWhere, orderBy);//owner.tablename

            switch (connType)
            {
                case "1"://SQL Server 
                    con = HIDB.common.make(HIDB.DBType.SQLServer, connstr);
                    break;
                case "2":
                    sql = string.Format("SELECT * FROM {0} where rownum<=13 {1} {2}",
                        viewName.Replace("[", "\"").Replace("]", "\""), sqlWhere.Replace("@", ":").Replace("+", "||"), orderBy);//owner.tablename
                    return DB.DBOracleConn(connstr, sql, para);
                    break;
                case "3":
                    string accConnstr = string.Format("Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Persist Security Info=False;", connstr);
                    con = HIDB.common.make(HIDB.DBType.OleDB, accConnstr);
                    break;
                case "4":
                    sql = string.Format("SELECT * FROM {0} where 1=1 {1} {2} limit 0,13", viewName.Replace("[", "`").Replace("]", "`"), sqlWhere.Replace("@", "?").Replace("'%'+", "concat(").Replace("+'%'", ",'%')"), orderBy);//owner.tablename
                    return DB.DBMySqlConn(connstr, sql, para);
                    break;
                case "8":
                    sql = string.Format("SELECT * FROM {0} where 1=1 {1} {2} limit 13", viewName.Replace("[", "\"").Replace("]", "\""), sqlWhere.Replace("@", ":").Replace("'%'+", "concat(").Replace("+'%'", ",'%')"), orderBy);//owner.tablename
                    return DB.DBNpgsqlConn(connstr, sql, para);
                    break;
                case "5":
                    string exlConnstr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES\";", connstr);
                    con = HIDB.common.make(HIDB.DBType.OleDB, exlConnstr);
                    break;
            }

            if (para == null)
                dtResult = con.GetDataTable(sql);
            else
                dtResult = con.GetDataTable(sql, para);
            return dtResult;
        }


        #region 读取本地映射数据表数据

        public DataTable GetLocalTableData(string tableName, string columns, string orderBy, bool asc, int recordCount, string[] sql, List<SqlParameter> para)
        {
            //创建数据库对象
            HIDB.dbo cmd = base.conn();
            try
            {
                string sqlWhere = string.Empty;
                if (sql.Length > 0)
                {
                    sqlWhere = string.Format(" where {0}", string.Join(" and ", sql));
                }
                string strSql = string.Format(@"SELECT Top {0} {1} FROM {2} {3}", recordCount, columns, tableName, sqlWhere);
                if (!string.IsNullOrEmpty(orderBy))
                {
                    strSql += string.Format(" ORDER BY {0} {1}", orderBy, (asc == true ? "ASC" : "DESC"));
                }

                DataTable dtc = cmd.GetDataTable(strSql, para.ToArray());
                return dtc;
            }
            catch (Exception ex)
            {
                Logger.LogError("GetLocalTableData", "GetLocalTableData", 0, 1, ex, "", "");
            }
            return new DataTable();
        }

        #endregion

        #endregion
    }
}